drop table account;
create table account(account_id bigint, acct_name text , acct_status varchar(8), client_id int, constraint pk_account primary key (account_id)) distribute by hash(account_id);

drop table transaction;
create table transaction(transaction_id varchar(40), tran_type varchar(8), branch_id int, account_id bigint, cp_account_id bigint, tran_date varchar(10), amount numeric, tran_time timestamp, teller_id int,constraint pk_transaction primary key (transaction_id)) distribute by hash(transaction_id);
create index idx_transation on transaction(account_id,cp_account_id,tran_date);

drop table entry;
create table entry(account_id bigint, transaction_id varchar(40),  transaction_datetime timestamp, amount numeric, tran_type varchar(8), branch_id int, tran_date varchar(10), constraint pk_entry primary key (account_id,transaction_id)) distribute by hash(account_id);
create index idx_entry on entry(transaction_datetime);
  
create or replace function qloudgen_acct_trans(p_i_transaction_id varchar(40), p_i_branch_id int, p_i_acct_id bigint, p_i_cp_acct_id bigint, p_i_tran_type varchar(8), p_i_amount numeric,p_i_teller_id int) returns int as $$
declare
    v_account_id  bigint;
	v_acct_bal  numeric;
begin
    insert into transaction(transaction_id,tran_type,branch_id,account_id,cp_account_id,tran_date,amount,tran_time,teller_id) 
                                        values(p_i_transaction_id,p_i_tran_type,p_i_branch_id,p_i_acct_id,p_i_cp_acct_id,to_char(now(),'yyyymmdd'),p_i_amount,now(),p_i_teller_id);
   if p_i_tran_type ='1' then
		insert into entry(account_id,transaction_id,transaction_datetime,amount,tran_type,branch_id,tran_date) values(p_i_acct_id,p_i_transaction_id,now(),p_i_amount,p_i_tran_type,p_i_branch_id,to_char(now(),'yyyymmdd'));
   elsif p_i_tran_type ='2' then
   --withdraw
		select account_id into v_account_id from account where account_id = p_i_acct_id for update;
		select sum(amount) into v_acct_bal from entry where account_id = p_i_acct_id;
		if v_acct_bal < p_i_amount then
		-- no available balance
			return -1;
		else
			insert into entry(account_id,transaction_id,transaction_datetime,amount,tran_type,branch_id,tran_date) values(p_i_acct_id,p_i_transaction_id,now(),-p_i_amount,p_i_tran_type,p_i_branch_id,to_char(now(),'yyyymmdd'));
		end if;
   elsif p_i_tran_type ='3' then
   --transfer
		select account_id into v_account_id from account where account_id = p_i_acct_id for update;
		select sum(amount) into v_acct_bal from entry where account_id = p_i_acct_id;
		if v_acct_bal < p_i_amount then
		-- no available balance
			return -1;
		else
			insert into entry(account_id,transaction_id,transaction_datetime,amount,tran_type,branch_id,tran_date) values(p_i_acct_id,p_i_transaction_id,now(),-p_i_amount,p_i_tran_type,p_i_branch_id,to_char(now(),'yyyymmdd'));
			insert into entry(account_id,transaction_id,transaction_datetime,amount,tran_type,branch_id,tran_date) values(p_i_cp_acct_id,p_i_transaction_id,now(),p_i_amount,p_i_tran_type,p_i_branch_id,to_char(now(),'yyyymmdd'));
		end if;
   end if;
   return 0;
end;
$$ language plpgsql;

create or replace function qloudgen_acct_trans_query (p_i_acct_id bigint, p_i_start_date varchar(10), p_i_end_date varchar(10)) returns setof transaction as $$ 
declare 
          ret record; 
begin 
	      if p_i_start_date is null and p_i_end_date is null then
				for ret in select * from transaction where account_id = p_i_acct_id or cp_account_id = p_i_acct_id  limit 5 loop
					return next ret; 
				end loop; 
		 elsif p_i_start_date is null and p_i_end_date is not null then
			    for ret in select * from transaction where (account_id = p_i_acct_id or cp_account_id = p_i_acct_id) and tran_date <= p_i_end_date loop
					return next ret; 
				end loop; 
		elsif p_i_start_date is not null and p_i_end_date is null then
			    for ret in select * from transaction where (account_id = p_i_acct_id or cp_account_id = p_i_acct_id) and tran_date >= p_i_start_date loop
					return next ret; 
				end loop; 
		elsif p_i_start_date is not null and p_i_end_date is not null then
			    for ret in select * from transaction where (account_id = p_i_acct_id or cp_account_id= p_i_acct_id) and tran_date <= p_i_end_date and tran_date >= p_i_start_date loop
					return next ret; 
				end loop; 
		end if;
		return; 
end; 
$$ language 'plpgsql' ;

create or replace function qloudgen_acct_trans_info(p_i_acct_id bigint) returns setof account as $$ 
declare 
          ret record; 
begin 
         for ret in select * from account where account_id = p_i_acct_id loop
                         return next ret; 
         end loop; 
return; 
end; 
$$ language 'plpgsql' ;

create or replace function qloudgen_acct_trans_bal(p_i_acct_id bigint) returns numeric as $$ 
declare 
          v_acct_bal  numeric;
begin 
    select sum(amount) into v_acct_bal from entry where account_id = p_i_acct_id;     
    return v_acct_bal;
end; 
$$ language 'plpgsql' ;
